﻿using System;
using System.Data.OleDb;
using System.Configuration;
using System.Collections.Generic;
using Business.Cdc;
using System.IO;

namespace DAO.Cdc
{
    public class CdcFilesDAO
    {
        public static void deleteFile(int id)
        {
            OleDbConnection conn = MainDAO.OpenCdcConnection();

            OleDbCommand myCommand = new OleDbCommand();
            myCommand.Connection = conn;
            myCommand.CommandText = "delete FROM FILES where ID = @id";
            myCommand.Parameters.Add("@id", OleDbType.Integer).Value = id;
            
            myCommand.ExecuteNonQuery();

            conn.Close();
        }

        public static List<FileDb> getFilesDb(Boolean onlyNotReported)
        {
            List<FileDb> _return = new List<FileDb>();

            OleDbConnection conn = MainDAO.OpenCdcConnection();

            String request = "select ID, FILE_SIZE, FUNCTIONNAL_DATE, IMPORT_DATE, TITLE, SUBJECT, REPORTED FROM FILES";
            if (onlyNotReported)
            {
                request += " where REPORTED=0";
            }
            OleDbCommand myCommand = new OleDbCommand(request, conn);

            OleDbDataReader dr = myCommand.ExecuteReader();
            while (dr.Read())
            {
                _return.Add(new FileDb(dr.GetInt32(0), dr.GetInt32(1), dr.GetDateTime(2), dr.GetDateTime(3), dr.GetString(4), dr.GetString(5), dr.GetBoolean(6)));
            }
            _return.Sort();
            dr.Close();
            conn.Close();

            return _return;
        }

        private static byte[] ReadFully(Stream input)
        {
            byte[] buffer = new byte[16 * 1024];
            using (MemoryStream ms = new MemoryStream())
            {
                int read;
                while ((read = input.Read(buffer, 0, buffer.Length)) > 0)
                {
                    ms.Write(buffer, 0, read);
                }
                return ms.ToArray();
            }
        }

        //public static int InsertData(Stream stream, String title, String subject, DateTime date)
        //{
        //    return InsertData(stream, title, subject, date, false);
        //}

        public static int InsertData(Stream stream, FileDb file)
        {
            OleDbConnection conn = MainDAO.OpenCdcConnection();

            //// Read in the file
            OleDbCommand myCommand = new OleDbCommand();
            byte[] readFully = ReadFully(stream);
            file.FileSize = readFully.Length;
            file.ImportDate = DateTime.Now;
            myCommand.CommandText = "insert into FILES(FILE, TITLE, SUBJECT, FUNCTIONNAL_DATE, IMPORT_DATE, REPORTED, FILE_SIZE) VALUES (@blob,@title,@subject,@date,@date2,@reported,@fileSize)";
            myCommand.Parameters.Add("@blob", OleDbType.Binary).Value = readFully;
            myCommand.Parameters.Add("@title", OleDbType.VarChar).Value = file.Title;
            myCommand.Parameters.Add("@subject", OleDbType.VarChar).Value = file.Subject;
            myCommand.Parameters.Add("@date", OleDbType.Date).Value = file.Date;
            myCommand.Parameters.Add("@date2", OleDbType.Date).Value = file.ImportDate;
            myCommand.Parameters.Add("@reported", OleDbType.Boolean).Value = file.Reported;
            myCommand.Parameters.Add("@fileSize", OleDbType.Integer).Value = file.FileSize;


            OleDbTransaction trans = conn.BeginTransaction();
            myCommand.Transaction = trans;
            myCommand.Connection = conn;
            myCommand.ExecuteNonQuery();
            OleDbCommand myOleDbCommand2;
            myOleDbCommand2 = new OleDbCommand("SELECT @@IDENTITY", conn);
            myOleDbCommand2.Transaction = trans;
            OleDbDataReader myOleDbDataReader = myOleDbCommand2.ExecuteReader();
            myOleDbDataReader.Read();

            int executeInsertGetIdentity = (int)myOleDbDataReader.GetValue(0);
            trans.Commit();
            myOleDbDataReader.Close();
            conn.Close();
            return executeInsertGetIdentity;
        }

        public static void ModifyFile(Stream stream, FileDb file)
        {
            OleDbConnection conn = MainDAO.OpenCdcConnection();

            //// Read in the file
            OleDbCommand myCommand = new OleDbCommand();
            byte[] readFully = ReadFully(stream);

            myCommand.CommandText = "UPDATE FILES "+
                "set TITLE=@title, SUBJECT=@subject, FUNCTIONNAL_DATE=@fDate, REPORTED=@reported "+
                ((stream != null) ? ", FILE=@blob":"") +
                " WHERE ID = @id";
            myCommand.Parameters.Add("@title", OleDbType.VarChar).Value = file.Title;
            myCommand.Parameters.Add("@subject", OleDbType.VarChar).Value = file.Subject;
            myCommand.Parameters.Add("@date", OleDbType.Date).Value = file.Date;
            myCommand.Parameters.Add("@reported", OleDbType.Boolean).Value = file.Reported;
            if (stream != null)
                myCommand.Parameters.Add("@blob", OleDbType.Binary).Value = readFully;
            myCommand.Parameters.Add("@id", OleDbType.Integer).Value = file.Id;
            
            myCommand.Connection = conn;
            try {
            myCommand.ExecuteNonQuery();
            } catch (Exception e) 
            {
                Console.WriteLine(e);
            }

            conn.Close();
        }


        public static byte[] getFile(int idFile)
        {
            OleDbConnection conn = MainDAO.OpenComptaConnection();
            OleDbCommand myOleDbCommand2 = new OleDbCommand("select FILE FROM FILES where ID = @idFile",
                conn);
            myOleDbCommand2.Parameters.Add("@idFile", OleDbType.Integer).Value = idFile;

            OleDbDataReader dr = myOleDbCommand2.ExecuteReader();

            while (dr.Read())
            {
                return (byte[])dr.GetValue(0);
            }
            dr.Close();
            conn.Close();
            return null;
        }
    }
}
